using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.Sql;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataLogicSQL
/// </summary>
public class DataLogicSQL
{
	public DataLogicSQL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static SqlCommand GetNewsSQL(bool IsPublished)
    {
        SqlCommand GetNewsCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());

        GetNewsCommand.CommandText = @"SELECT id, 
                                        NewsTitle,
                                        REPLACE(NewsTitle,' ','-') AS NewsTitleLinks,
                                        DAY(NewsDate) AS date,
                                        MONTH(NewsDate) AS month,
                                        YEAR(NewsDate) AS year,
                                        NewsCaption,
                                        News,
                                        NewsDate,
                                        IsPublished,
                                        PhotoAvail,
                                        DATEDIFF(day, NewsDate, GETDATE()) AS dayDiff,
                                        DATEDIFF(hh,NewsDate, GETDATE()) AS HourDiff
                                        FROM  News
                                        WHERE (IsPublished =@IsPublished) ORDER BY NewsDate DESC";
        GetNewsCommand.Connection = DBConnection;
        GetNewsCommand.Parameters.AddWithValue("@IsPublished", IsPublished);

        return GetNewsCommand;
    }

    public static SqlCommand IsTagAvailSQL(string TagName)
    {
        SqlCommand IsTagAvail = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());

        IsTagAvail.CommandText = "SELECT * FROM BlogTag WHERE TagName=@TagName";
        IsTagAvail.Connection = DBConnection;
        IsTagAvail.Parameters.AddWithValue("@TagName", TagName);
        return IsTagAvail;
    }

    public static SqlCommand GetLoginSQL(string username, string password)
    {
        SqlCommand loginCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());

        loginCommand.CommandText = "SELECT * FROM Users WHERE username=@username and password=@password";
        loginCommand.Connection = DBConnection;
        loginCommand.Parameters.AddWithValue("@username", username);
        loginCommand.Parameters.AddWithValue("@password", password);
        return loginCommand;
    }
    
    public static SqlCommand GetMaxMinSQL()
    {
        SqlCommand GetMaxMinCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        GetMaxMinCommand.CommandText = "SELECT Max(TagCount) AS MaxTagCount,Min(TagCount)As MinTagCount from BlogTag";
        GetMaxMinCommand.Connection = DBConnection;
        return GetMaxMinCommand;
    }

    public static SqlCommand GetAllTagsSQL()
    {
        SqlCommand GetAllTagsCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        GetAllTagsCommand.CommandText = "SELECT TagName,TagCount from BlogTag";
        GetAllTagsCommand.Connection = DBConnection;
        return GetAllTagsCommand;
    }

  
    public static SqlCommand GetTagCountSQL(string singleTag)
    {
        SqlCommand GetTagCountCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        GetTagCountCommand.CommandText = "SELECT TagCount FROM BlogTag WHERE TagName=@singleTag";
        GetTagCountCommand.Connection = DBConnection;
        GetTagCountCommand.Parameters.AddWithValue("@singleTag", singleTag);
        return GetTagCountCommand;
    }

    /// <summary>
    /// Update Tagcount for a specific tag
    /// </summary>
    /// <param name="TagName"></param>
    /// <param name="TagCount"></param>
    /// <returns></returns>
    public static SqlCommand SetTagCountSQL(string TagName, int TagCount)
    {
        SqlCommand SetTagCountCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        SetTagCountCommand.CommandText = "Update BlogTag set TagCount=@TagCount WHERE TagName=@TagName";
        SetTagCountCommand.Connection = DBConnection;
        SetTagCountCommand.Parameters.AddWithValue("@TagName", TagName);
        SetTagCountCommand.Parameters.AddWithValue("@TagCount", TagCount);
        return SetTagCountCommand;
    }


    public static SqlCommand GetBlogReadSQL()
    {
        SqlCommand GetBlogReadSQLCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        GetBlogReadSQLCommand.CommandText = @"SELECT BlogID,
                                            PostTitle,
                                            REPLACE(PostTitle, ' ', '-') AS PostTitleLinks, 
                                            PostContent, 
                                            PostDate, 
                                            DAY(PostDate) AS date,
                                            MONTH(PostDate) AS month,
                                            YEAR(PostDate) AS year,
                                            PostedBy,
                                            PostCaption,
                                            PostTags, 
                                            (SELECT COUNT(commentID) AS Expr1 
                                                FROM BlogComment WHERE (Blog.BlogID = BlogID)) AS CommentCount
                                            FROM Blog ORDER BY PostDate DESC";
        GetBlogReadSQLCommand.Connection = DBConnection;
        return GetBlogReadSQLCommand;
    }

    /// <summary>
    /// Insert a new tag
    /// </summary>
    /// <param name="TagName"></param>
    /// <returns></returns>
    public static SqlCommand InsertNewTagSQL(string TagName)
    {
        SqlCommand InsertNewTagCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        InsertNewTagCommand.CommandText = "Insert into BlogTag (TagName,TagCount)values (@TagName,@TagCount)";
        InsertNewTagCommand.Connection = DBConnection;
        InsertNewTagCommand.Parameters.AddWithValue("@TagName", TagName);
        InsertNewTagCommand.Parameters.AddWithValue("@TagCount", 1);
        return InsertNewTagCommand;
    }

    public static SqlCommand GetConectByPageAndItemSQL(string pageName, string itemName)
    {
        SqlCommand GetConectByPageAndItemSQLCommand = new SqlCommand();
        SqlConnection DBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString.ToString());
        GetConectByPageAndItemSQLCommand.CommandText = @"SELECT * 
                                            FROM Content
                                            WHERE PageName=@PageName
                                            AND ItemName=@ItemName";
        GetConectByPageAndItemSQLCommand.Connection = DBConnection;
        GetConectByPageAndItemSQLCommand.Parameters.AddWithValue("@PageName", pageName);
        GetConectByPageAndItemSQLCommand.Parameters.AddWithValue("@ItemName", itemName);


        return GetConectByPageAndItemSQLCommand; 
    }

}
